﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using RedirectManager.Interfaces;
using Sitecore.Diagnostics;

namespace RedirectManager.Providers.Sql
{
    using RedirectManager.Pipelines.PublishItem;

    /// <summary>
    /// SQL Lookup provider
    /// </summary>
    public class LookupProvider : ILookupProvider
    {
        private static readonly object Lock = new object();

        private static Dictionary<string, Redirect> pathDictionary;

        private static Dictionary<string, Redirect> PathDictionary
        {
            get
            {
                // double-check locking
                if (pathDictionary == null)
                {
                    // prevents concurrent calls to LoadAll();
                    lock (Lock)
                    {
                        // double-check locking
                        if (pathDictionary == null)
                        {
                            var stopwatch = new Stopwatch();
                            stopwatch.Start();
                            pathDictionary = LoadAll();
                            stopwatch.Stop();
                            Log.Info(string.Format("Redirect Manager SQL Provider populated Url lookup dictionary with {0} items in {1}ms", pathDictionary.Count, stopwatch.ElapsedMilliseconds), typeof(LookupProvider));
                        }
                    }
                }

                return pathDictionary;
            }
        }

        private static string ConnectionString
        {
            get
            {
                var config = ConfigurationManager.ConnectionStrings["RedirectManager"];
                Assert.IsNotNull(config, "Redirect Manager SQL Provider: connection string entry not found for 'RedirectManager'");
                var connStr = config.ConnectionString;
                Assert.IsNotNullOrEmpty(connStr, "Redirect Manager SQL Prrovider: connection string value empty");
                return connStr;
            }
        } 
  
        #region Implementation of ILookupProvider

        public bool IsReadOnly
        {
            get
            {
                return false;
            }
        }

        public IRedirect LookupUrl(string requestPath)
        {
            // The main lookup method. Returns the redirect or null if not found.
            return PathDictionary.ContainsKey(requestPath) ? PathDictionary[requestPath] : null;
        }

        public IEnumerable<IRedirect> LookupItem(string id)
        {
            var data = new List<Redirect>();

            // Create new SqlConnection object.
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                // Create new SqlCommand object.
                using (var command = new SqlCommand())
                {
                    command.Parameters.Add(new SqlParameter("ResponseTargetId", DbType.Guid));
                    command.Parameters["ResponseTargetId"].Value = new Guid(id);
                    command.CommandText = "SELECT * FROM Redirects WHERE ResponseTargetId = @ResponseTargetId";
                    command.Connection = conn;

                    var reader = command.ExecuteReader();

                    // Get ordinals outside of loop
                    var responseTargetIdOrdinal = reader.GetOrdinal("ResponseTargetId");
                    var requestPathOrdinal = reader.GetOrdinal("RequestPath");
                    var responseStatusCodeOrdinal = reader.GetOrdinal("ResponseStatusCode");
                    var sitesOrdinal = reader.GetOrdinal("Sites");

                    while (reader.Read())
                    {
                        var redirect = new Redirect(
                            true,
                            reader.GetGuid(responseTargetIdOrdinal).ToString(),
                            reader.GetString(requestPathOrdinal),
                            reader.IsDBNull(sitesOrdinal) ? null : reader.GetString(sitesOrdinal),
                            reader.GetInt32(responseStatusCodeOrdinal));

                        data.Add(redirect);
                    }
                }
            }

            return data.ToArray();
        }

        public bool Exists(string requestUrlPath)
        {
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                // Create new SqlCommand object.
                using (var command = new SqlCommand())
                {
                    command.Connection = conn;
                    command.Parameters.Add(new SqlParameter("RequestPath", SqlDbType.NVarChar));
                    command.Parameters["RequestPath"].Value = requestUrlPath;

                    command.CommandText = "SELECT COUNT(*) FROM Redirects WHERE RequestPath = @RequestPath";
                    var rows = (int)command.ExecuteScalar();
                    if (rows > 0)
                    {
                        return true;
                    }

                    return false;
                }
            }
        }

        //public ExistsResponse Exists(string requestUrlPath, string responseTargetId)
        //{
        //    using (var conn = new SqlConnection(ConnectionString))
        //    {
        //        conn.Open();

        //        // Create new SqlCommand object.
        //        using (var command = new SqlCommand())
        //        {
        //            command.Parameters.Add(new SqlParameter("ResponseTargetId", SqlDbType.UniqueIdentifier));
        //            command.Parameters.Add(new SqlParameter("RequestPath", SqlDbType.NVarChar));

        //            command.Parameters["ResponseTargetId"].Value = new Guid(responseTargetId);
        //            command.Parameters["RequestPath"].Value = requestUrlPath;

        //            command.CommandText = "SELECT REDIRECTS WHERE RequestPath = @RequestPath";
        //            var dataReader = command.ExecuteReader();
        //            if (!dataReader.HasRows)
        //            {
        //                return ExistsResponse.NotFound;
        //            }

        //            if (((Guid)dataReader["ResponseTargetId"]).Equals(responseTargetId))
        //            {
        //                return ExistsResponse.Found;
        //            }

        //            return ExistsResponse.FoundDifferent;
        //        }
        //    }
        //}

        public void Create(string requestUrlPath, string responseTargetId, bool autoGenerated)
        {
            // Create new SqlConnection object.
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                // Create new SqlCommand object.
                using (var command = new SqlCommand())
                {
                    command.Parameters.Add(new SqlParameter("ResponseTargetId", SqlDbType.UniqueIdentifier));
                    command.Parameters.Add(new SqlParameter("RequestPath", SqlDbType.NVarChar));
                    command.Parameters.Add(new SqlParameter("ResponseStatusCode", SqlDbType.Int));
                    command.Parameters.Add(new SqlParameter("AutoGenerated", SqlDbType.Bit));

                    command.Parameters["ResponseTargetId"].Value = new Guid(responseTargetId);
                    command.Parameters["RequestPath"].Value = requestUrlPath;
                    command.Parameters["ResponseStatusCode"].Value = 301;
                    command.Parameters["AutoGenerated"].Value = autoGenerated;

                    command.CommandText = "INSERT INTO Redirects (ResponseTargetId, RequestPath, ResponseStatusCode, AutoGenerated) VALUES (@ResponseTargetId, @RequestPath, @ResponseStatusCode, @AutoGenerated)";
                    command.Connection = conn;

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        if (ex.Number == 8152) 
                        {
                            // 8152 = data trucation, where url path is longer than the DB column allows
                            throw new ArgumentException(string.Format("Redirect Manager: Url path '{0}' is longer ({1} chars) than the configured SQL data column will allow.\r\nIncrease column length or shorten item path.", requestUrlPath, requestUrlPath.Length), ex);
                        }
                        
                        throw;
                    }
                }
            }
        }

        public void Delete(string requestPath)
        {
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                // Create new SqlCommand object.
                using (var command = new SqlCommand())
                {
                    command.Parameters.Add(new SqlParameter("RequestPath", SqlDbType.NVarChar));
                    command.Parameters["RequestPath"].Value = requestPath;

                    command.CommandText = "DELETE FROM Redirects WHERE RequestPath = @RequestPath";
                    command.Connection = conn;
                    command.ExecuteNonQuery();
                }
            }
        }

        #endregion

        public void ClearCache(object sender, EventArgs args)
        {
            // Clear dictionary to force reload.
            pathDictionary = null;
        }

        private static Dictionary<string, Redirect> LoadAll()
        {
            // Load all redirects
            var data = new Dictionary<string, Redirect>(StringComparer.OrdinalIgnoreCase);

            // Create new SqlConnection object.
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                // Create new SqlCommand object.
                using (var command = new SqlCommand("SELECT * FROM Redirects", conn))
                {
                    // Invoke ExecuteReader method.
                    var reader = command.ExecuteReader();

                    // Do slow ordinal lookup outside of loop
                    var responseTargetIdOrdinal = reader.GetOrdinal("ResponseTargetId");
                    var requestPathOrdinal = reader.GetOrdinal("RequestPath");
                    var responseStatusCodeOrdinal = reader.GetOrdinal("ResponseStatusCode");
                    var sitesOrdinal = reader.GetOrdinal("Sites");

                    // Iterate, create redirects and assign to data
                    while (reader.Read())
                    {
                        var redirect = new Redirect(
                            true,
                            reader.GetGuid(responseTargetIdOrdinal).ToString(),
                            reader.GetString(requestPathOrdinal),
                            reader.IsDBNull(sitesOrdinal) ? null : reader.GetString(sitesOrdinal),
                            reader.GetInt32(responseStatusCodeOrdinal));

                        // Check path doesn't already exist
                        if (!data.ContainsKey(redirect.RequestPath))
                        {
                            // Add to dictionary
                            data[redirect.RequestPath] = redirect;
                        }
                        else
                        {
                            // Log duplicates (this should not happen)
                            Log.Warn(string.Format("RedirectManager Sql Provider duplicate request path skipped : {0}", redirect.RequestPath), typeof(LookupProvider));
                        }
                    }
                }
            }

            return data;
        }

        public void CreateSqlTestData(string targetItemId, int count)
        {
            var rnd = new Random();
            var target = Sitecore.Configuration.Factory.GetDatabase("master").GetItem(targetItemId);
            if (target != null)
            {
                for (int i = 0; i < count; i++)
                {
                    var name = this.RandomString(12, rnd);
                    if (!this.Exists(name))
                    {
                        this.Create("/test-" + name, target.ID.ToString(), true);
                    }
                }
            }
        }

        private string RandomString(int size, Random rnd)
        {
            const string Chars = "abcdefghijklmnopqrstuvwxyz";
            var buffer = new char[size];
            for (int i = 0; i < size; i++)
            {
                buffer[i] = Chars[rnd.Next(Chars.Length)];
            }

            return new string(buffer);
        }
    }
}
